package cn.yunhe.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.yunhe.model.Cate;

public class CateDaoImpl implements ICateDao {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public int addCate(Cate cate) {
		conn=BaseDB.getConnection();
		int flag=0;
		try {
			String sql="insert into t_cate values(seq_news_cate.nextval,?)";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, cate.getCateName());
			
			flag= pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	
	public List<Cate> searchAll() {
		conn=BaseDB.getConnection();
		List<Cate> list= new ArrayList<Cate>();
		
		try {
			String sql="select * from t_cate order by cid desc";
			
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next()){
				Cate c=new Cate();
				c.setCateId(rs.getInt("cid"));
				c.setCateName(rs.getString("cname"));
				list.add(c);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	public Cate getCateById(int cateId) {
		conn=BaseDB.getConnection();
		Cate cate=null;
		try {
			String sql="select * from t_cate where cid=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1,cateId);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				cate=new Cate();
				cate.setCateId(rs.getInt("cid"));
				cate.setCateName(rs.getString("cname"));
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cate;
	}



	public int updateCateById(Cate cate) {
		
		String sql="update t_cate set cname =? where cid=?";
		Object[] params={cate.getCateName(),cate.getCateId()};
		
		return BaseDB.executeUpdate(sql, params);
	}


	
	public int deleteCateById(int cateid) {
		String sql="delete from t_cate where cid=?";
		Object[] params={cateid};
		return BaseDB.executeUpdate(sql, params);
	}


	public int getCateCount() {
		int count =0;
		conn=BaseDB.getConnection();
		try {
			String sql="select count(1) from t_cate";
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				count=rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		return count;
	}



	public List<Cate> searchByPage(int pageSize, int currPage) {
		conn=BaseDB.getConnection();
		List<Cate> list= new ArrayList<Cate>();
		
		try {
			String sql="select * from (select t.*,rownum rn from (select * from t_cate order by cid desc) t where rownum <= ?) where rn>= ?";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1,pageSize*currPage);
			pstmt.setInt(2, pageSize*(currPage-1)+1);
			
			rs=pstmt.executeQuery();
			
			while(rs.next()){
				Cate c=new Cate();
				c.setCateId(rs.getInt("cid"));
				c.setCateName(rs.getString("cname"));
				list.add(c);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	
	}


	@Override
	public List<Cate> searchByCate(Cate cate) {
		// TODO Auto-generated method stub
		String sql="select * from t_cate where 1=1";
		if(cate.getCateId()!=null){
			sql+=" and cid="+cate.getCateId();
		}
		if(cate.getCateName()!=null){
			sql+=" and cname='"+cate.getCateName()+"'";
		}
		return null;
	}

}
 